package com.ops.uitl.jdbc;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import org.apache.log4j.Logger;
import com.ops.uitl.common.Common;
import com.ops.uitl.data.DataMap;
import com.ops.uitl.data.IData;
import com.ops.uitl.data.IDataset;

public class DaoHelper {

	protected static Common common = Common.getInstance();

	public static final int COLUMN_TYPE_STRING = 1;
	public static final int COLUMN_TYPE_NUMBER = 2;
	public static final int COLUMN_TYPE_DATETIME = 3;
	public static final int COLUMN_TYPE_OTHER = 4;

	private static int database;
	
	static{
		try {
			database = Integer.parseInt(common.getProperty("database/type", String.valueOf(BaseFactory.DATABASE_TYPE_ORACLE)));
		} catch (Exception e) {
			Logger.getLogger(DaoHelper.class).error("[ERROR] get database error");
		}
	}
	
	/**
	 * get database
	 * 
	 * @return String
	 */
	public static int getDatabase() throws Exception {
		return database;
	}
	
	/**
	 * get execute time by sql
	 * 
	 * @param log
	 * @param beginTime
	 * @throws Exception
	 */
	public static void getExecuteTimeBySql(Logger log, long beginTime) throws Exception {
		if (log.isDebugEnabled()) log.debug("SQL execute time: " + ((double) (System.currentTimeMillis() - beginTime) / (double) 1000) + "s");
	}
	
	/**
	 * get date format
	 * 
	 * @param value
	 * @return String
	 * @throws Exception
	 */
	public static String getDateFormat(String value) throws Exception {
		switch (getDatabase()) {
			case BaseFactory.DATABASE_TYPE_ORACLE:
				switch (value.length()) {
					case 4:
						return "yyyy";
					case 6:
						return "yyyymm";
					case 7:
						return "yyyy-mm";
					case 8:
						return "yyyymmdd";
					case 10:
						return "yyyy-mm-dd";
					case 13:
						return "yyyy-mm-dd hh24";
					case 16:
						return "yyyy-mm-dd hh24:mi";
					case 19:
						return "yyyy-mm-dd hh24:mi:ss";
				}
			case BaseFactory.DATABASE_TYPE_MYSQL:
				switch (value.length()) {
					case 4:
						return "%Y";
					case 6:
						return "%Y%m";
					case 7:
						return "%Y-%m";
					case 8:
						return "%Y%m%d";
					case 10:
						return "%Y-%m-%d";
					case 13:
						return "%Y-%m-%d %H";
					case 16:
						return "%Y-%m-%d %H:%i";
					case 19:
						return "%Y-%m-%d %H:%i:%s";
				}
		}
		return null;
	}
	
	/**
	 * oper date
	 * 
	 * @param value
	 * @param interval(+|-)
	 * @param date_type
	 * @return String
	 * @throws Exception
	 */
	public static String oper_date(String value, int interval, String date_type) throws Exception {		
		String type = date_type.toUpperCase();
		String operate = interval > 0 ? "+" : "-";
		
		switch (getDatabase()) {
			case BaseFactory.DATABASE_TYPE_ORACLE:
				if ("YEAR".equals(type)) {
					return "add_months(" + value + "," + interval * 12 + ")";
				} else if ("MONTH".equals(type)) {
					return "add_months(" + value + "," + interval + ")";
				} else if ("DAY".equals(type)) {
					return value + operate + Math.abs(interval);
				} else if ("HOUR".equals(type)) {
					return value + operate + Math.abs(interval / 24);
				} else if ("MINUTE".equals(type)) {
					return value + operate + Math.abs(interval / (24 * 60));
				} else if ("SECOND".equals(type)) {
					return value + operate + Math.abs(interval / (24 * 60 * 60));
				}
			case BaseFactory.DATABASE_TYPE_MYSQL:
				return value + " " + operate + " INTERVAL " + Math.abs(interval) + " " + date_type;
		}
		
		return null;
	}
	
	/**
	 * to_days
	 * 
	 * @param value
	 * @return String
	 * @throws Exception
	 */
	public static String to_days(String value) throws Exception {
		switch (getDatabase()) {
			case BaseFactory.DATABASE_TYPE_ORACLE:
				return value;
			case BaseFactory.DATABASE_TYPE_MYSQL:
				return "to_days(" + value + ")";
		}
		return null;
	}
	
	/**
	 * get rownum func
	 * 
	 * @return String
	 * @throws Exception
	 */
	public static String getRownumFunc() throws Exception {
		switch (getDatabase()) {
			case BaseFactory.DATABASE_TYPE_ORACLE:
				return "rownum";
			case BaseFactory.DATABASE_TYPE_MYSQL:
				return "rownum()";
		}
		return null;
	}
	
	/**
	 * get rownum func
	 * 
	 * @param rownum
	 * @return String
	 * @throws Exception
	 */
	public static String getRownumFunc(int rownum) throws Exception {
		switch (getDatabase()) {
			case BaseFactory.DATABASE_TYPE_ORACLE:
				return "rownum = " + rownum;
			case BaseFactory.DATABASE_TYPE_MYSQL:
				return "limit " + rownum;
		}
		return null;
	}
	
	/**
	 * get sysdate func
	 * 
	 * @return String
	 * @throws Exception
	 */
	public static String getSysdateFunc() throws Exception {
		switch (getDatabase()) {
			case BaseFactory.DATABASE_TYPE_ORACLE:
				return "sysdate";
			case BaseFactory.DATABASE_TYPE_MYSQL:
				return "now()";
		}
		return null;
	}
	
	
	/**
	 * get sequence func
	 * 
	 * @param sequence
	 * @return String
	 * @throws Exception
	 */
	public static String getSequenceFunc(String sequence) throws Exception {
		switch (getDatabase()) {
			case BaseFactory.DATABASE_TYPE_ORACLE:
				return sequence + ".nextval";
			case BaseFactory.DATABASE_TYPE_MYSQL:
				return "nextval('" + sequence + "')";
		}
		return null;
	}
	
	/**
	 * get current time sql
	 * 
	 * @return String
	 * @throws Exception
	 */
	public static String getCurrentTimeSql() throws Exception {
		switch (getDatabase()) {
			case BaseFactory.DATABASE_TYPE_ORACLE:
				return "select " + getSysdateFunc() + " from dual";
			case BaseFactory.DATABASE_TYPE_MYSQL:
				return "select " + getSysdateFunc();
		}
		return null;
	}

	/**
	 * get sequence sql
	 * 
	 * @param sequence
	 * @return String
	 * @throws Exception
	 */
	public static String getSequenceSql(String sequence) throws Exception {
		switch (getDatabase()) {
			case BaseFactory.DATABASE_TYPE_ORACLE:
				return "select " + getSequenceFunc(sequence) + " from dual";
			case BaseFactory.DATABASE_TYPE_MYSQL:
				return "select " + getSequenceFunc(sequence);
		}
		return null;
	}

	/**
	 * get sequence sql
	 * 
	 * @param eparchy_code
	 * @param sequence
	 * @return String
	 * @throws Exception
	 */
	public static String getSequenceSql(String eparchy_code, String sequence) throws Exception {
		switch (getDatabase()) {
			case BaseFactory.DATABASE_TYPE_ORACLE:
				return "select F_SYS_GETSEQID('" + eparchy_code + "', '" + sequence + "') from dual";
			case BaseFactory.DATABASE_TYPE_MYSQL:
				return "select F_SYS_GETSEQID('" + eparchy_code + "', '" + sequence + "')";
		}
		return null;
	}
	
	/**
	 * get tochar sql
	 * 
	 * @param value
	 * @param format
	 * @return String
	 * @throws Exception
	 */
	public static String getTocharSql(String value, String format) throws Exception {
		String dformat = getDateFormat(format);
		return "to_char(" + value + ", '" + dformat + "')";
	}

	/**
	 * get count sql
	 * 
	 * @param sql
	 * @return String
	 */
	public static String getCountSql1(String sql) throws Exception {
		Pattern pattern = Pattern.compile("(?i)(?u)(union|join|group by)");
		Matcher matcher = pattern.matcher(sql);
		if (!matcher.find()) {
			pattern = Pattern.compile("(?i)(?u)(select.*?from)");
			matcher = pattern.matcher(sql);
			if (matcher.find()) {
				String subsql = matcher.group();
				Pattern subpattern = Pattern.compile("(?i)(?u)(/\\*.*?\\*/)");
				Matcher submatcher = subpattern.matcher(subsql);

				StringBuffer specstr = new StringBuffer();
				while (submatcher.find()) {
					specstr.append(submatcher.group());
				}

				sql = matcher.replaceFirst("select " + specstr
						+ " count(*) from");
				pattern = Pattern.compile("(?i)(?u)(order by.*$)");
				matcher = pattern.matcher(sql);
				return matcher.replaceAll("");
			}
		}
		return "select count(*) from (" + sql + ")";
	}

	public static String getCountSql(String s) {
		StringBuffer sbf = new StringBuffer();
		sbf.append("SELECT COUNT(1) FROM (");
		sbf.append(s);
		sbf.append(")");
		String sf = sbf.toString();
		StringBuffer sb = new StringBuffer();
		String[] delims = { " ", "\t", "\n", "\r", "\f", ",", "(+)", "||",
				"**", "<=", ">=", "!=", "<>", ":=", "<", ">", "=", "+", "-",
				"*", "/", "(", ")" };
		List tokens = tokenize(s, delims);
		String token;

		if ((tokens.size() < 4)
				|| (tokens.get(0).toString().compareToIgnoreCase("SELECT") != 0)
				|| (tokens.get(1).toString().compareToIgnoreCase("DISTINCT") == 0)) {
			return sf;
		}

		int i = 0;
		int len = tokens.size();
		int subLevel = 0;
		int state = 0;
		int idxFrom = -1;
		int idxWhere = -1;
		int idxGroup = -1;
		int idxOrder = -1;
		int idxUnion = -1;
		while (i < len) {
			token = (String) tokens.get(i);
			if (token.equals("(")) {
				subLevel++;
			} else if (token.equals(")")) {
				subLevel--;
			} else if ((state == 0) && (subLevel == 0)
					&& (token.compareToIgnoreCase("FROM") == 0)) {
				idxFrom = i;
				state = 1;
			} else if ((state == 1) && (subLevel == 0)
					&& (token.compareToIgnoreCase("WHERE") == 0)) {
				idxWhere = i;
			} else if ((state == 1) && (subLevel == 0)
					&& (token.compareToIgnoreCase("ORDER") == 0)) {
				idxOrder = i;
			} else if ((state == 1) && (subLevel == 0)
					&& (token.compareToIgnoreCase("GROUP") == 0)) {
				idxGroup = i;
			} else if ((state == 1) && (subLevel == 0)
					&& (token.compareToIgnoreCase("UNION") == 0)) {
				idxUnion = i;
			} else if ((state == 0) && token.equals("?")) {
				// like such case:
				// SELECT ? stat_month,user_id,......
				// fallback
				return sf;
			}
			i++;
		}
		if ((idxFrom < 0) || (idxGroup > 0) || (idxUnion > 0)) {
			return sf;
		}
		sb.append("SELECT ");
		for (i = 0; i < idxFrom; i++) {
			token = (String) tokens.get(i);
			if (token.startsWith("/*")) {
				sb.append(" ");
				sb.append(token);
			}
		}
		sb.append(" COUNT(1)");
		for (i = idxFrom; i < len; i++) {
			token = (String) tokens.get(i);
			if (!token.startsWith("(")) sb.append(" ");
			sb.append(token);
		}
		return sb.toString();
	}

	static void checkAddString(List ls, Object s) {
		if (s.toString().trim().length() > 0) {
			ls.add(s.toString().trim());
		}
	}

	static List tokenize(String s, String[] delims) {
		List ls = new ArrayList();
		StringBuffer sb = new StringBuffer();
		StringBuffer sbRemark = new StringBuffer();
		StringBuffer sbQuote = new StringBuffer();
		boolean quote = false;
		boolean remark = false;
		int len = s.length();
		int i = 0;
		String delim = "";
		while (i < len) {
			char c = s.charAt(i);
			if (quote) {
				if (c == '\'') {
					if (i + 1 < len) {
						char cn = s.charAt(i + 1);
						if (cn == '\'') {
							sbQuote.append(c);
							sbQuote.append(cn);
							i++;
						} else {
							sbQuote.append(c);
							checkAddString(ls, sbQuote);
							sbQuote = new StringBuffer();
							quote = false;
						}
					} else {
						sbQuote.append(c);
						checkAddString(ls, sbQuote);
						sbQuote = new StringBuffer();
						quote = false;
					}
				} else {
					sbQuote.append(c);
				}
			} else {
				if (remark) {
					if (c == '*') {
						if (i + 1 < len) {
							char cn = s.charAt(i + 1);
							if (cn == '/') {
								sbRemark.append(c);
								sbRemark.append(cn);
								checkAddString(ls, sbRemark);
								sbRemark = new StringBuffer();
								remark = false;
								i++;
							} else {
								sbRemark.append(c);
							}
						} else {
							sbRemark.append(c);
						}
					} else {
						sbRemark.append(c);
					}
				} else {
					if (c == '\'') {
						checkAddString(ls, sb);
						sb = new StringBuffer();
						sbQuote.append(c);
						quote = true;
					} else if ((c == '/') && (i + 1 < len)
							&& (s.charAt(i + 1) == '*')) {
						checkAddString(ls, sb);
						sb = new StringBuffer();
						sbRemark.append("/*");
						remark = true;
						i++;
					} else {
						int sep = 0;
						for (int j = 0; j < delims.length; j++) {
							if (s.substring(i).startsWith(delims[j])) {
								sep = delims[j].length();
								delim = delims[j];
								break;
							}
						}
						if (sep > 0) {
							checkAddString(ls, sb);
							checkAddString(ls, delim);
							sb = new StringBuffer();
							i += sep - 1;
						} else {
							sb.append(c);
						}
					}
				}
			}
			i++;
		}
		checkAddString(ls, sb);
		if (quote) {
			throw new RuntimeException("quoted string not properly terminated");
		}
		if (remark) {
			throw new RuntimeException("remark not properly terminated");
		}
		return ls;
	}

	/**
	 * get paging sql
	 * 
	 * @param sql
	 * @param param
	 * @param start
	 * @param end
	 * @return String
	 */
	public static String getPagingSql(String sql, Parameter param, int start, int end) throws Exception {
		StringBuffer str = new StringBuffer();
		
		switch (getDatabase()) {
			case BaseFactory.DATABASE_TYPE_ORACLE:
				str.append("select * from (select row_.*, rownum rownum_ from (");
				str.append(sql);
				str.append(") row_ where rownum <= ?) where rownum_ > ?");
				param.add(String.valueOf(end));
				param.add(String.valueOf(start));
				break;
			case BaseFactory.DATABASE_TYPE_MYSQL:
				str.append(sql);
				str.append(" limit ?, ?");
				param.add(String.valueOf(start));
				param.add(String.valueOf(end));
				break;
		}
			
		return str.toString();
	}

	/**
	 * get paging sql
	 * 
	 * @param sql
	 * @param param
	 * @param start
	 * @param end
	 * @return String
	 * @throws Exception
	 */
	public static String getPagingSql(String sql, IData param, int start, int end) throws Exception {
		StringBuffer str = new StringBuffer();
		
		switch (getDatabase()) {
			case BaseFactory.DATABASE_TYPE_ORACLE:
				str.append("select * from (select row_.*, rownum rownum_ from (");
				str.append(sql);
				str.append(") row_ where rownum <= :MAX_NUM) where rownum_ > :MIN_NUM");
				break;
			case BaseFactory.DATABASE_TYPE_MYSQL:
				str.append(sql);
				str.append(" limit :MIN_NUM, :MAX_NUM");
				break;
		}
		
		param.put("MIN_NUM", String.valueOf(start));
		param.put("MAX_NUM", String.valueOf(end));
		
		return str.toString();
	}

	/**
	 * get insert sql
	 * 
	 * @param table_name
	 * @param namestr
	 * @param valuestr
	 * @return String
	 */
	public static String getInsertSql(String table_name, String namestr,
			String valuestr) {
		StringBuffer str = new StringBuffer();
		str.append("insert into " + table_name);
		str.append("(" + namestr + ")");
		str.append(" values ");
		str.append("(" + valuestr + ")");
		return str.toString();
	}

	/**
	 * get update sql
	 * 
	 * @param table_name
	 * @param setstr
	 * @param keystr
	 * @return String
	 */
	public static String getUpdateSql(String table_name, String setstr,
			String keystr) {
		StringBuffer str = new StringBuffer();
		str.append("update " + table_name + " set ");
		str.append(setstr);
		str.append(" where ");
		str.append(keystr);
		return str.toString();
	}

	/**
	 * get delete sql
	 * 
	 * @param table_name
	 * @param setstr
	 * @param keystr
	 * @return String
	 */
	public static String getDeleteSql(String table_name, String keystr) {
		StringBuffer str = new StringBuffer();
		str.append("delete from " + table_name);
		str.append(" where ");
		str.append(keystr);
		return str.toString();
	}

	/**
	 * get query sql
	 * 
	 * @param table_name
	 * @param setstr
	 * @param keystr
	 * @return String
	 */
	public static String getQuerySql(String table_name, String keystr) {
		StringBuffer str = new StringBuffer();
		str.append("select * from " + table_name);
		str.append(" where ");
		str.append(keystr);
		return str.toString();
	}

	/**
	 * get objects by column
	 * 
	 * @param column
	 * @param data
	 * @return Object[]
	 * @throws Exception
	 */
	public static Object[] getObjectsByColumn(IColumn column, IData data)
			throws Exception {
		String columnName = column.getColumnName();
		Object columnValue = data.get(columnName);

		if (isDatetimeColumn(column.getColumnType())) {
			if (columnValue != null) {
				if ("".equals(columnValue)) {
					columnValue = null;
				} else {
					columnValue = common.encodeTimestamp(columnValue.toString());
				}
			}
		} else if (column.getColumnType() == Types.LONGVARCHAR) {
			if (columnValue != null && !"".equals(columnValue)) {
				columnValue = new ColumnObject(columnName, columnValue, Types.LONGVARCHAR);
			}
		}
		
		return new Object[] { columnName, columnValue };
	}

	/**
	 * get data by keys
	 * 
	 * @param keys
	 * @param value
	 * @return IData
	 * @throws Exception
	 */
	public static IData getDataByKeys(String[] keys, String[] values)
			throws Exception {
		IData data = new DataMap();
		for (int i = 0; i < keys.length; i++) {
			data.put(keys[i], values[i]);
		}
		return data;
	}

	/**
	 * get objects by insert
	 * 
	 * @param conn
	 * @param table_name
	 * @param data
	 * @return Object[]
	 * @throws Exception
	 */
	public static Object[] getObjectsByInsert(Connection conn,
			String table_name, IData data) throws Exception {
		StringBuffer namestr = new StringBuffer();
		StringBuffer valuestr = new StringBuffer();
		Parameter param = new Parameter();

		IColumn[] columns = getColumns(conn, table_name);
		for (int i = 0; i < columns.length; i++) {
			Object[] colobjs = getObjectsByColumn(columns[i], data);

			namestr.append(colobjs[0] + ",");
			valuestr.append("?" + ",");
			param.add(colobjs[1]);
		}

		return new Object[] {
				getInsertSql(table_name, common.trimSuffix(namestr.toString(),
						","), common.trimSuffix(valuestr.toString(), ",")),
				param };
	}

	/**
	 * get objects by insert
	 * 
	 * @param conn
	 * @param table_name
	 * @param dataset
	 * @return Object[]
	 * @throws Exception
	 */
	public static Object[] getObjectsByInsert(Connection conn,
			String table_name, IDataset dataset) throws Exception {
		StringBuffer namestr = new StringBuffer();
		StringBuffer valuestr = new StringBuffer();
		IColumn[] columns = getColumns(conn, table_name);

		Parameter[] params = new Parameter[dataset.size()];
		for (int i = 0; i < params.length; i++) {
			IData data = (IData) dataset.get(i);
			params[i] = new Parameter();

			for (int j = 0; j < columns.length; j++) {
				Object[] colobjs = getObjectsByColumn(columns[j], data);

				if (i == 0) {
					namestr.append(colobjs[0] + ",");
					valuestr.append("?" + ",");
				}
				params[i].add(colobjs[1]);
			}
		}

		return new Object[] {
				getInsertSql(table_name, common.trimSuffix(namestr.toString(),
						","), common.trimSuffix(valuestr.toString(), ",")),
				params };
	}

	/**
	 * get objects by update
	 * 
	 * @param conn
	 * @param table_name
	 * @param data
	 * @return Object[]
	 * @throws Exception
	 */
	public static Object[] getObjectsByUpdate(Connection conn,
			String table_name, IData data) throws Exception {
		return getObjectsByUpdate(conn, table_name, data, null);
	}

	/**
	 * get object by update
	 * 
	 * @param conn
	 * @param table_name
	 * @param dataset
	 * @return Object[]
	 * @throws Exception
	 */
	public static Object[] getObjectsByUpdate(Connection conn,
			String table_name, IDataset dataset) throws Exception {
		return getObjectsByUpdate(conn, table_name, dataset, null);
	}

	/**
	 * get objects by update
	 * 
	 * @param conn
	 * @param table_name
	 * @param data
	 * @param keys
	 * @return Object[]
	 * @throws Exception
	 */
	public static Object[] getObjectsByUpdate(Connection conn,
			String table_name, IData data, String[] keys) throws Exception {
		return getObjectsByUpdate(conn, table_name, data, keys, null);
	}

	/**
	 * get objects by update
	 * 
	 * @param conn
	 * @param table_name
	 * @param data
	 * @param keys
	 * @param values
	 * @return Object[]
	 * @throws Exception
	 */
	public static Object[] getObjectsByUpdate(Connection conn,
			String table_name, IData data, String[] keys, String[] values)
			throws Exception {
		StringBuffer setstr = new StringBuffer();
		Parameter param = new Parameter();

		IColumn[] columns = getColumns(conn, table_name);
		for (int i = 0; i < columns.length; i++) {
			Object[] colobjs = getObjectsByColumn(columns[i], data);

			setstr.append(colobjs[0] + " = " + "?" + ",");
			param.add(colobjs[1]);
		}

		Object[] keyobjs = getObjectsByKeys(conn, table_name, keys,
				values == null ? data : getDataByKeys(keys, values));

		param.addAll((Parameter) keyobjs[1]);

		return new Object[] {
				getUpdateSql(table_name, common.trimSuffix(setstr.toString(),
						","), (String) keyobjs[0]), param };
	}

	/**
	 * get objects by update
	 * 
	 * @param conn
	 * @param table_name
	 * @param dataset
	 * @param keys
	 * @return Object[]
	 * @throws Exception
	 */
	public static Object[] getObjectsByUpdate(Connection conn,
			String table_name, IDataset dataset, String[] keys)
			throws Exception {
		StringBuffer setstr = new StringBuffer();
		StringBuffer keystr = new StringBuffer();
		IColumn[] columns = getColumns(conn, table_name);

		Parameter[] params = new Parameter[dataset.size()];
		for (int i = 0; i < params.length; i++) {
			IData data = (IData) dataset.get(i);
			params[i] = new Parameter();

			for (int j = 0; j < columns.length; j++) {
				Object[] colobjs = getObjectsByColumn(columns[j], data);

				if (i == 0)
					setstr.append(colobjs[0] + " = " + "?" + ",");
				params[i].add(colobjs[1]);
			}

			Object[] keyobjs = getObjectsByKeys(conn, table_name, keys, data);
			params[i].addAll((Parameter) keyobjs[1]);

			if (i == 0)
				keystr.append(keyobjs[0]);
		}

		return new Object[] {
				getUpdateSql(table_name, common.trimSuffix(setstr.toString(),
						","), keystr.toString()), params };
	}

	/**
	 * get objects by delete
	 * 
	 * @param conn
	 * @param table_name
	 * @param data
	 * @return Object[]
	 * @throws Exception
	 */
	public static Object[] getObjectsByDelete(Connection conn,
			String table_name, IData data) throws Exception {
		return getObjectsByDelete(conn, table_name, data, null);
	}

	/**
	 * get objects by delete
	 * 
	 * @param conn
	 * @param table_name
	 * @param dataset
	 * @return Object[]
	 * @throws Exception
	 */
	public static Object[] getObjectsByDelete(Connection conn,
			String table_name, IDataset dataset) throws Exception {
		return getObjectsByDelete(conn, table_name, dataset, null);
	}

	/**
	 * get objects by delete
	 * 
	 * @param conn
	 * @param table_name
	 * @param data
	 * @param keys
	 * @return Object[]
	 * @throws Exception
	 */
	public static Object[] getObjectsByDelete(Connection conn,
			String table_name, IData data, String[] keys) throws Exception {
		Object[] keyobjs = getObjectsByKeys(conn, table_name, keys, data);
		return new Object[] { getDeleteSql(table_name, (String) keyobjs[0]),
				(Parameter) keyobjs[1] };
	}

	/**
	 * get objects by delete
	 * 
	 * @param conn
	 * @param table_name
	 * @param dataset
	 * @param keys
	 * @return Object[]
	 * @throws Exception
	 */
	public static Object[] getObjectsByDelete(Connection conn,
			String table_name, IDataset dataset, String[] keys)
			throws Exception {
		StringBuffer keysql = new StringBuffer();

		Parameter[] params = new Parameter[dataset.size()];
		for (int i = 0; i < params.length; i++) {
			Object[] keyobjs = getObjectsByKeys(conn, table_name, keys,
					(IData) dataset.get(i));

			if (i == 0)
				keysql.append(keyobjs[0]);
			params[i] = (Parameter) keyobjs[1];
		}

		return new Object[] { getDeleteSql(table_name, keysql.toString()),
				params };
	}

	/**
	 * get objects by query
	 * 
	 * @param conn
	 * @param table_name
	 * @param data
	 * @return Object[]
	 * @throws Exception
	 */
	public static Object[] getObjectsByQuery(Connection conn,
			String table_name, IData data) throws Exception {
		return getObjectsByQuery(conn, table_name, data, null);
	}

	/**
	 * get objects by query
	 * 
	 * @param conn
	 * @param table_name
	 * @param data
	 * @param keys
	 * @return Object[]
	 * @throws Exception
	 */
	public static Object[] getObjectsByQuery(Connection conn,
			String table_name, IData data, String[] keys) throws Exception {
		Object[] keyobjs = getObjectsByKeys(conn, table_name, keys, data);
		return new Object[] { getQuerySql(table_name, (String) keyobjs[0]),
				(Parameter) keyobjs[1] };
	}

	/**
	 * get object by keys
	 * 
	 * @param conn
	 * @param table_name
	 * @param keys
	 * @param data
	 * @return Object[]
	 * @throws Exception
	 */
	public static Object[] getObjectsByKeys(Connection conn, String table_name,
			String[] keys, IData data) throws Exception {
		if (keys == null)
			keys = getPrimaryKeys(conn, table_name);

		IData columns = getColumnsByData(conn, table_name);

		StringBuffer sqlstr = new StringBuffer();
		Parameter param = new Parameter();

		for (int i = 0; i < keys.length; i++) {
			String key = keys[i];
			Object value = data.get(key);

			IColumn column = (IColumn) columns.get(key.toUpperCase());
			sqlstr.append(" and "
					+ (isDatetimeColumn(column.getColumnType()) ? getTocharSql(key,
							value.toString()) : key) + " = " + "?");
			param.add(value);
		}

		return new Object[] { common.trimPrefix(sqlstr.toString(), " and "),
				param };
	}

	/**
	 * get primary keys
	 * 
	 * @param conn
	 * @param table_name
	 * @param columns
	 * @return String[]
	 * @throws Exception
	 */
	public static String[] getPrimaryKeys(Connection conn, String table_name)
			throws Exception {

		IData columns = getColumnsByData(conn, table_name);

		List keys = new ArrayList();

		ResultSet rs = conn.getMetaData().getPrimaryKeys(null, "%",
				table_name.toUpperCase());
		while (rs.next()) {
			String column_name = rs.getString("COLUMN_NAME").toUpperCase();
			if (columns.containsKey(column_name) && !keys.contains(column_name))
				keys.add(column_name);
		}
		rs.close();

		String[] primaryKeys = (String[]) keys.toArray(new String[0]);

		return primaryKeys;
	}

	/**
	 * get the columns
	 * 
	 * @param conn
	 * @param table_name
	 * @return IColumn[]
	 * @throws Exception
	 */
	public static IColumn[] getColumns(Connection conn, String table_name)
			throws Exception {
		IData columns = getColumnsByData(conn, table_name);
		List keys = Arrays.asList(getPrimaryKeys(conn, table_name));

		String[] colnames = columns.getNames();
		IColumn[] IColumns = new IColumn[colnames.length];
		for (int i = 0; i < colnames.length; i++) {
			IColumn column = (IColumn) columns.get(colnames[i]);
			column.setKey(keys.contains(column.getColumnName()) ? true : false);
			IColumns[i] = column;
		}

		return IColumns;
	}

	/**
	 * get columns by data
	 * 
	 * @param conn
	 * @param table_name
	 * @return IData
	 * @throws Exception
	 */
	public static IData getColumnsByData(Connection conn, String table_name)
			throws Exception {
		IData columns = getColumnsByResult(conn, table_name);
		return columns;
	}

	/**
	 * get columns by result
	 * 
	 * @param conn
	 * @param table_name
	 * @return IData
	 * @throws Exception
	 */
	public static IData getColumnsByResult(Connection conn, String table_name)
			throws Exception {
		IData columns = new DataMap();

		PreparedStatement statement = conn.prepareStatement("select * from "
				+ table_name.toUpperCase() + " where 1 = 0");
		ResultSetMetaData metaData = statement.executeQuery().getMetaData();
		for (int i = 1; i <= metaData.getColumnCount(); i++) {
			IColumn column = new ColumnInfo();
			column.setColumnName(metaData.getColumnName(i).toUpperCase());
			column.setColumnType(metaData.getColumnType(i));
			column.setColumnDesc(metaData.getColumnLabel(i));
			column.setColumnSize(metaData.getColumnDisplaySize(i));
			column.setDecimalDigits(metaData.getScale(i));
			column
					.setNullable(metaData.isNullable(i) == ResultSetMetaData.columnNoNulls ? false
							: true);

			columns.put(column.getColumnName(), column);
		}
		statement.close();

		return columns;
	}

	/**
	 * get columns by database
	 * 
	 * @param conn
	 * @param table_name
	 * @return IData
	 * @throws Exception
	 */
	public static IData getColumnsByDatabase(Connection conn, String table_name)
			throws Exception {
		IData columns = getColumnsByResult(conn, table_name);

		ResultSet rs = conn.getMetaData().getColumns(null, "%",
				table_name.toUpperCase(), null);
		while (rs.next()) {
			String column_name = rs.getString("COLUMN_NAME").toUpperCase();
			if (columns.containsKey(column_name)) {
				IColumn column = (IColumn) columns.get(column_name);
				column.setColumnDesc(rs.getString("REMARKS"));
			}
		}

		return columns;
	}
	
	/**
	 * 
	 * @param conn
	 * @param sql
	 * @return
	 * @throws Exception
	 */
	public static IData getColumnsBySQL(Connection conn, String sql) throws Exception {
		IData columns = new DataMap();
		
		String nsql = "select * from ("+sql+") where 1 = 0";
		PreparedStatement statement = conn.prepareStatement(nsql);
		
		ResultSetMetaData metaData = statement.executeQuery().getMetaData();
		
		for (int i = 1; i <= metaData.getColumnCount(); i++) {
			IColumn column = new ColumnInfo();
			column.setColumnName(metaData.getColumnName(i).toUpperCase());
			column.setColumnType(metaData.getColumnType(i));
			column.setColumnDesc(metaData.getColumnLabel(i));
			column.setColumnSize(metaData.getColumnDisplaySize(i));
			column.setDecimalDigits(metaData.getScale(i));
			column
					.setNullable(metaData.isNullable(i) == ResultSetMetaData.columnNoNulls ? false
							: true);

			columns.put(column.getColumnName(), column);
		}
		statement.close();
		return columns;
	}
	
	
	/**
	 * getTablesByDatabase
	 * @param conn
	 * @param catalog
	 * @param schema
	 * @param name
	 * @return
	 * @throws Exception
	 */
	public static IData getTablesByDatabase(Connection conn, String catalog, String schema, String name) throws Exception {
		String username = conn.getMetaData().getUserName().toUpperCase();
		
		IData tables = getTablesByResult(conn, catalog, schema, name);
		return tables;
	}
	
	/**
	 * getTablesByDatabase
	 * @param conn
	 * @param catalog
	 * @param schema
	 * @param name
	 * @param types
	 * @return
	 * @throws Exception
	 */
	public static IData getTablesByResult(Connection conn, String catalog, String schema, String name) throws Exception {
		IData tables = new DataMap();
		ResultSet rs = conn.getMetaData().getTables(catalog, schema, name, null);
		String username = conn.getMetaData().getUserName().toUpperCase();

		while (rs.next()) {
			if (!username.equals(rs.getString("TABLE_SCHEM")))
				continue;
			
			String table_type = rs.getString("TABLE_TYPE");
			if (!"SYNONYM".equals(table_type) && !"TABLE".equals(table_type))
				continue;
			
			ITable table = new TableInfo();
			table.setTableCat(rs.getString("TABLE_CAT"));
			table.setTableSchem(rs.getString("TABLE_SCHEM"));
			table.setTableName(rs.getString("TABLE_NAME"));
			table.setTableType(table_type);
			table.setRemarks(rs.getString("REMARKS"));
			tables.put(table.getTableName(), table);
		}
		
		return tables;
	}

	/**
	 * is datetime column
	 * 
	 * @return boolean
	 * @throws Exception
	 */
	public static boolean isDatetimeColumn(int columnType) throws Exception {
		return columnType == Types.DATE || columnType == Types.TIME
				|| columnType == Types.TIMESTAMP;
	}

	/**
	 * get result value
	 * 
	 * @param rs
	 * @param type
	 * @param name
	 * @return Object
	 * @throws Exception
	 */
	public static Object getValueByResultSet(ResultSet rs, int type, String name)
			throws Exception {
		if (type == Types.BLOB) {
			return rs.getBlob(name);
		} else {
			return rs.getString(name);
		}
	}

	public static void decodeParamInfo(String[] paramNames, int[] paramKinds,
			int[] paramTypes) {
		for (int i = 0; i < paramNames.length; i++) {
			paramKinds[i] = decodeParamKind(paramNames[i]);
			paramTypes[i] = decodeParamType(paramNames[i]);
		}
	}

	public static int decodeParamKind(String paramName) {
		int v;
		char c = paramName.charAt(0);
		switch (c) {
		case 'i':
			v = 0;
			break;
		case 'o':
			v = 1;
			break;
		default:
			v = 2;
		}
		return v;
	}

	public static int decodeParamType(String paramName) {
		int v;
		char c = paramName.charAt(1);
		switch (c) {
		case 'n':
			v = Types.NUMERIC;
			break;
		case 'd':
			v = Types.TIMESTAMP;
			break;
		case 'v':
		default:
			v = Types.VARCHAR;
		}
		return v;
	}

	public static Object callFunc(Connection conn, String name,
			String[] paramNames, IData params, int returnType)
			throws SQLException {
		int[] paramKinds = new int[paramNames.length];
		int[] paramTypes = new int[paramNames.length];
		decodeParamInfo(paramNames, paramKinds, paramTypes);
		return callFunc(conn, name, paramNames, params, paramKinds, paramTypes,
				returnType);
	}


	static Connection doGetNativeConnection(Connection conn)
			throws SQLException {
		System.out.println("sss:"+conn.getClass().getName());
		return conn;
	}


	public static void callProc(Connection conn, String name,
			String[] paramNames, IData params) throws SQLException {
		int[] paramKinds = new int[paramNames.length];
		int[] paramTypes = new int[paramNames.length];
		decodeParamInfo(paramNames, paramKinds, paramTypes);
		callProc(conn, name, paramNames, params, paramKinds, paramTypes);
	}

	/**
	 * ���ô洢����(����)
	 * 
	 * @return Object - �洢���̷��ص�ֵ, ������returnType����
	 * @param conn -
	 *            JDBC����
	 * @param name -
	 *            �洢��������
	 * @param paramNames -
	 *            �����������飬������洢���������Ĳ���˳��һ�£����ֲ�һ��Ҫ����̲�����һ��
	 * @param params -
	 *            ���ÿ��������Ӧ������ֵ��������ɺ󱣴��Ӧ���������ֵ�����ֱ�����paramNames��������һ��
	 * @param paramKinds -
	 *            ������������������飬��������ÿ������������������ͣ�0:IN 1:OUT 2:IN OUT
	 * @param paramTypes -
	 *            ���������������飬һ������ÿ���������������ͣ��ο�java.sql.Types�еĳ���
	 * @param returnType -
	 *            �洢���̷���ֵ���������ͣ��ο�java.sql.Types�еĳ���
	 * @throws SQLException
	 */
	public static Object callFunc(Connection conn, String name,
			String[] paramNames, IData params, int[] paramKinds,
			int[] paramTypes, int returnType) throws SQLException {
		StringBuffer sb = new StringBuffer();
		sb.append("{?=call ");
		sb.append(name);
		sb.append("(");
		int i;
		for (i = 0; i < paramNames.length; i++) {
			sb.append("?");
			if (i < paramNames.length - 1) {
				sb.append(",");
			}
		}
		sb.append(")}");
		CallableStatement stmt = conn.prepareCall(sb.toString());
		stmt.registerOutParameter(1, returnType);
		for (i = 0; i < paramNames.length; i++) {
			String paramName = paramNames[i];
			int paramKind = paramKinds[i];
			int paramIdx = i + 2;
			if ((paramKind == 0) || (paramKind == 2)) {
				stmt.setObject(paramIdx, params.get(paramName), paramTypes[i]);
			}
			if ((paramKind == 1) || (paramKind == 2)) {
				stmt.registerOutParameter(paramIdx, paramTypes[i]);
			}
		}
		stmt.execute();
		for (i = 0; i < paramNames.length; i++) {
			String paramName = paramNames[i];
			int paramKind = paramKinds[i];
			int paramIdx = i + 2;
			if ((paramKind == 1) || (paramKind == 2)) {
				params.put(paramName, stmt.getObject(paramIdx));
			}
		}
		Object retval = stmt.getObject(1);
		stmt.close();
		return retval;
	}

	/**
	 * ���ô洢����
	 * 
	 * @param conn -
	 *            JDBC����
	 * @param name -
	 *            �洢��������
	 * @param paramNames -
	 *            �����������飬������洢���������Ĳ���˳��һ�£����ֲ�һ��Ҫ����̲�����һ��
	 * @param params -
	 *            ���ÿ��������Ӧ������ֵ��������ɺ󱣴��Ӧ���������ֵ�����ֱ�����paramNames��������һ��
	 * @param paramKinds -
	 *            ������������������飬��������ÿ������������������ͣ�0:IN 1:OUT 2:IN OUT
	 * @param paramTypes -
	 *            ���������������飬һ������ÿ���������������ͣ��ο�java.sql.Types�еĳ���
	 * @throws SQLException
	 */
	public static void callProc(Connection conn, String name,
			String[] paramNames, IData params, int[] paramKinds,
			int[] paramTypes) throws SQLException {
		StringBuffer sb = new StringBuffer();
		sb.append("{call ");
		sb.append(name);
		sb.append("(");
		int i;
		for (i = 0; i < paramNames.length; i++) {
			sb.append("?");
			if (i < paramNames.length - 1) {
				sb.append(",");
			}
		}
		sb.append(")}");
		CallableStatement stmt = conn.prepareCall(sb.toString());
		for (i = 0; i < paramNames.length; i++) {
			String paramName = paramNames[i];
			int paramKind = paramKinds[i];
			int paramIdx = i + 1;
			if ((paramKind == 0) || (paramKind == 2)) {
				stmt.setObject(paramIdx, params.get(paramName), paramTypes[i]);
			}
			if ((paramKind == 1) || (paramKind == 2)) {
				stmt.registerOutParameter(paramIdx, paramTypes[i]);
			}
		}
		stmt.execute();
		for (i = 0; i < paramNames.length; i++) {
			String paramName = paramNames[i];
			int paramKind = paramKinds[i];
			int paramIdx = i + 1;
			if ((paramKind == 1) || (paramKind == 2)) {
				params.put(paramName, stmt.getObject(paramIdx));
			}
		}
		stmt.close();
	}

}